const router = require('koa-router')()
const { query } = require('../db')


router.get('/api/getUserList', async (ctx, next) => {
    // 获取用户列表
    const data = await query('select * from userlist')

    ctx.body = {
        code: 1,
        data
    }
})

router.get('/api/insert', async (ctx) => {
    // 添加
    await query("insert into tablelist values ('王五',14, '男', null)")

    ctx.body = {
        code: 1,
        msg: '添加成功'
    }
})

// 按条件添加
router.get('/api/insertPage', async (ctx) => {
    await query("insert into tablelist (name, age) values ('赵六', 30)")

    ctx.body = {
        code: 1,
        msg: '添加成功'
    }
})

// 搜索全部
router.get('/api/select',  async (ctx) => {
    const data = await query('select * from tablelist')

    ctx.body = {
        code: 1,
        data
    }
})

// 按名字搜索 只返回名字
router.get('/api/selectName', async (ctx) => {
    const data = await query('select name, age from tablelist');

    ctx.body = {
        code: 1,
        data
    }
})


// 精准搜索
router.post('/api/selectNameAll', async (ctx) => {
    const { name } = ctx.request.body; 

    const data = await query(`select * from tablelist where name="${name}"`)

    ctx.body = {
        code: 1,
        data
    }
})

// 多条件搜索 and
router.post('/api/selectAnd', async (ctx) => {
    const { name, age } = ctx.request.body;

    const data = await query(`select * from tablelist where name="${name}" and age=${age}`)

    ctx.body = {
        code: 1,
        data
    }
})

// 多条件查找 满足任意条件
router.post('/api/selectOr', async (ctx) => {
    const { name,age } = ctx.request.body; 

    const data = await query(`select * from tablelist where name="${name}" or age=${age}`)

    ctx.body = {
        code: 1,
        data
    }
})

// 模糊搜索
router.post('/api/selectLink', async (ctx) => {
    const { name } = ctx.request.body;

    const data = await query(`select * from tablelist where name like "%${name}%"`)

    ctx.body = {
        code: 1,
        data
    }
})

// 单条件更新
router.post('/api/update', async (ctx) => {
    const { name, id } = ctx.request.body;

    await query(`update tablelist set name="${name}" where id=${id}`)

    ctx.body = {
        code: 1,
        msg: '更新成功'
    }
})

// 多条件更新
router.post('/api/updates', async (ctx) => {
    const { name, age, id } = ctx.request.body;

    await query(`update tablelist set name="${name}", age=${age} where id=${id}`)

    ctx.body = {
        code: 1,
        msg: '更新成功'
    }
})

// 删除某一条数据
router.post('/api/delete', async (ctx) => {
    const { id } = ctx.request.body

    await query(`delete from tablelist where id=${id}`)

    ctx.body = {
        code: 1,
        msg: '删除成功'
    }
})


// 清空所有数据
router.get('/api/deleteAll', async (ctx) => {
    await query('delete from tablelist');

    ctx.body = {
        code: 1,
        msg: '删除成功'
    }
})

module.exports = router
